package com.group15.phms;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper{
	
	//@Override
	//public void onOpen(SQLiteDatabase db)
	//{
	//	db.execSQL("PRAGMA foreign_keys = ON;"); 
	//}
	
	private static final int DATABASE_VERSION=1;
	private static final String DATABASE_NAME = "healthManager";
    
    //Table Create Statements
	//Create Table for Users
    private static final String CREATE_TABLE_USERS = "CREATE TABLE users " 
    		+ "(name TEXT, " 
    		+ "username TEXT PRIMARY KEY, " 
    		+ "password TEXT, "
    		+ "email TEXT, " 
    		+ "birthday TEXT)";
    //Create Table for Doctors
    private static final String CREATE_TABLE_DOCTORS = "CREATE TABLE doctors " 
    		+ "(name TEXT, " 
    		+ "email TEXT, " 
    		+ "doctorUsername TEXT, " 
    		+ "FOREIGN KEY(doctorUsername) REFERENCES users(username), "
    		+ "PRIMARY KEY(name, doctorUsername))";
    //Create Table for Contacts
    private static final String CREATE_TABLE_CONTACTS = "CREATE TABLE contacts " 
    		+ "(name TEXT, " 
    		+ "email TEXT, "
    		+ "phone TEXT, "
    		+ "contactUsername TEXT, " 
    		+ "FOREIGN KEY(contactUsername) REFERENCES users(username), "
    		+ "PRIMARY KEY(name, contactUsername))";
    
    
  //Create Table for user_medications
    private static final String CREATE_TABLE_MEDICATIONS = "CREATE TABLE medications " 
    		+ "(medicationUsername TEXT, " 
    		+ "genericName TEXT, "
    		+ "name TEXT, "
    		+ "FOREIGN KEY (medicationUsername) REFERENCES users(username), "
    		+ "PRIMARY KEY(medicationUsername, genericName))";
    
    //Create Table for notes
    private static final String CREATE_TABLE_NOTES = "CREATE TABLE notes " 
    		+ "(title TEXT, " 
    		+ "body TEXT, "
    		+ "noteUsername TEXT, "
    		+ "FOREIGN KEY (noteUsername) REFERENCES users(username), "
    		+ "PRIMARY KEY(noteUsername, title))";
    
    //Create Table for Doses
    private static final String CREATE_TABLE_DOSES = "CREATE TABLE doses " 
    		+ "(doseUsername TEXT, "
    		+ "doseGenericName TEXT, "
    		+ "hour INTEGER, "
    		+ "minute INTEGER, "
    		+ "amount TEXT, " 
    		+ "type TEXT, "
    		+ "retry INTEGER, "
    		+ "FOREIGN KEY(doseUsername) REFERENCES users(username), "
    		+ "FOREIGN KEY(doseGenericName) REFERENCES medications(genericName), "
    		+ "PRIMARY KEY(doseUsername, doseGenericName, hour, minute))";

  //Create Table for Vitals
    private static final String CREATE_TABLE_VITALS = "CREATE TABLE vitals " 
    		+ "(vitalsUsername TEXT, "
    		+ "cholesterol INTEGER, "
    		+ "glucose INTEGER, "
    		+ "systolic INTEGER, "
    		+ "diastolic INTEGER, " 
    		+ "year INTEGER, "
    		+ "month INTEGER, "
    		+ "day INTEGER)";
    
  //Create Table for Meals
    private static final String CREATE_TABLE_MEALS = "CREATE TABLE meals " 
    		+ "(mealsUsername TEXT, "
    		+ "meal TEXT, "
    		+ "date TEXT, "
    		+ "cal TEXT, "
    		+ "note TEXT)";
    
    //Create Table for Weights
    private static final String CREATE_TABLE_WEIGHTS = "CREATE TABLE weights " 
    		+ "(weight TEXT, "
    		+ "date TEXT, "
    		+ "health TEXT, "
    		+ "note TEXT)";
    
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
 
        // creating required tables
        db.execSQL(CREATE_TABLE_USERS);
        db.execSQL(CREATE_TABLE_DOCTORS);
        db.execSQL(CREATE_TABLE_CONTACTS);
        db.execSQL(CREATE_TABLE_MEDICATIONS);
        db.execSQL(CREATE_TABLE_DOSES);
        db.execSQL(CREATE_TABLE_VITALS);
        db.execSQL(CREATE_TABLE_NOTES);
        db.execSQL(CREATE_TABLE_MEALS);
        db.execSQL(CREATE_TABLE_WEIGHTS);
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // on upgrade drop older tables
    	db.execSQL("DROP TABLE IF EXISTS doses");
    	db.execSQL("DROP TABLE IF EXISTS medications");
    	db.execSQL("DROP TABLE IF EXISTS contacts");
    	db.execSQL("DROP TABLE IF EXISTS doctors");
    	db.execSQL("DROP TABLE IF EXISTS users");
    	db.execSQL("DROP TABLE IF EXISTS vitals");
    	db.execSQL("DROP TABLE IF EXISTS notes");
        db.execSQL("DROP TABLE IF EXISTS meals");
        db.execSQL("DROP TABLE IF EXISTS weights");
 
        // create new tables
        onCreate(db);
    }
 
    //----------------CRUD Operations Below-----------------//
    
    //add note
    void addNote(String username, Note n)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
   	 
        ContentValues values = new ContentValues();
        values.put("title", n.getTitle()); // note title
        values.put("body", n.getBody()); // note body
        values.put("noteUsername", username); // note body
        // Inserting Row
        db.insert("notes", null, values);
    }
    
    //retrieve a note
    Note getNote(String username, String title)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query="SELECT * FROM notes WHERE title = \"" + title + "\" AND noteUsername = \"" + username + "\"";
    	Cursor c = db.rawQuery(query, null);
    	if (c != null)
            c.moveToFirst();
    	Note n = new Note();
    	n.setTitle(c.getString(c.getColumnIndex("title")));
    	n.setBody(c.getString(c.getColumnIndex("body")));
    	return n;
    }
    
    //retrieve note titles 
    List<String> getNoteTitles(String username)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
    	List<String> titles = new ArrayList<String>();
    	String query="SELECT title FROM notes WHERE noteUsername = \"" + username + "\"";
    	 // looping through all rows and adding to list
    	Cursor c = db.rawQuery(query, null);
    	if (c.moveToFirst()) {
            do { titles.add(c.getString(c.getColumnIndex("title")));} 
            while (c.moveToNext());
        }
    	return titles;
    } 
    
    //delete a note
    void deleteNote(String username, String title)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query;
    	
    	//delete doses
    	query="DELETE FROM notes " 
    			+ "WHERE noteUsername = \"" + username +"\" "
    			+ "AND title = \"" + title +"\"";
    	db.execSQL(query);
    }
    
    //update a note
    void updateNote(String username, Note old, Note n)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
        String query="UPDATE notes "
        		+ "SET title = \"" + n.getTitle() +"\", "
        		+ "body = \"" + n.getBody() +"\" "
        		+ "WHERE title = \"" + old.getTitle() + "\" AND noteUsername = \"" + username + "\"";
        db.execSQL(query);
    }
    
    //add User, returns username upon success
    String addUser(User u)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	 
        ContentValues values = new ContentValues();
        values.put("name", u.getName()); // User Name
        values.put("username", u.getUserName()); // User Username
        values.put("password", u.getPassword()); //User Password
        values.put("email", u.getEmail()); //User Email
        values.put("birthday", u.getBirthday()); //User Birthday
 
        // Inserting Row
        db.insert("users", null, values);
        return u.getUserName();
    }
    
    //retrieve a user
    User getUser(String username)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query="SELECT * FROM users WHERE username = \"" + username + "\"";
    	Cursor c = db.rawQuery(query, null);
    	if (c != null)
            c.moveToFirst();
    	User u = new User();
    	u.setName(c.getString(c.getColumnIndex("name")));
    	u.setUserName(c.getString(c.getColumnIndex("username")));
    	u.setPassword(c.getString(c.getColumnIndex("password")));
    	u.setEmail(c.getString(c.getColumnIndex("email")));
    	u.setBirthday(c.getString(c.getColumnIndex("birthday")));
    	return u;
    }
    
    //add Medication, returns genericName upon success
    String addMedication(Medication m, String username)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	
    	ContentValues values;
    	//check if user already has medication stored. If not, add
    	if(getMedicationInfo(m.getGenName(), username)==null)
    	{
    		values = new ContentValues();
    		values.put("medicationUsername", username); // Username who uses medication
            values.put("name", m.getName()); // Medication Name
            values.put("genericName",m.getGenName()); // Medication generic name
            // Inserting Row
            db.insert("medications", null, values);
            return m.getGenName();
    	}
    	return null; 
    }
    
    //Retrieve medication info from database
    Medication getMedicationInfo(String genericName, String username)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query="SELECT name, genericName FROM medications "
    			+ "WHERE genericName = \"" +genericName + "\" AND medicationUsername = \"" +username + "\"";
    	Cursor c = db.rawQuery(query, null);
    	if (c != null)
    	{
            if(c.moveToFirst()==true)
            {
	            Medication m = new Medication();
	            m.setName(c.getString(c.getColumnIndex("name")));
	            m.setGenName(c.getString(c.getColumnIndex("genericName")));
	            return m;
            }
            return null;
    	}
    	return null;
    }
    
    
    //retrieve list of medications for a user
    List<Medication> getMedications(String username)
    {
    	List<Medication> medications = new ArrayList<Medication>();

    	String query="SELECT name, genericName " 
    			+ "FROM medications "
    			+ "WHERE medicationUsername = \"" + username +"\"";	
    	
    	SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(query, null);

        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                Medication m = new Medication();
                m.setName(c.getString(c.getColumnIndex("name")));
                m.setGenName(c.getString(c.getColumnIndex("genericName")));
                // adding to medications list
                medications.add(m);
            } while (c.moveToNext());
        }
    	return medications;
    }
    
    //delete a user's medication
    void deleteMedication(String genName, String username)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query;
    	
    	//delete doses
    	query="DELETE FROM doses " 
    			+ "WHERE doseUsername = \"" + username +"\" "
    			+ "AND doseGenericName = \"" + genName +"\"";
    	db.execSQL(query);
    	
    	//delete medication
    	query="DELETE FROM medications " 
    			+ "WHERE genericName = \"" + genName + "\" AND medicationUsername = \"" + username + "\"";
    	db.execSQL(query);
    }
    
    
    //add Doctor, returns name on success
    String addDoctor(Doctor d, String username)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	 
        ContentValues values = new ContentValues();
        values.put("name", d.getName()); // Doctor Name
        values.put("email",d.getEmail()); // Doctor Email
        values.put("doctorUserName",username); // User for this Doctor

        // Inserting Row
        try {db.insertOrThrow("doctors", null, values);}
        catch (android.database.sqlite.SQLiteConstraintException ex){ return ex.toString(); }
        return "success";
    }
    
    void deleteDoctor(String doctorName, String username)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query;
    	
    	//delete doses
    	query="DELETE FROM doctors " 
    			+ "WHERE name = \"" + doctorName +"\" "
    			+ "AND doctorUsername = \"" + username +"\"";
    	db.execSQL(query);
    }
    
    //Retrieve doctor info from database
    Doctor getDoctorInfo(String name, String username)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query="SELECT * FROM doctors WHERE doctorUserName = \"" + username + "\" AND name = \"" + name + "\"";
    	Cursor c = db.rawQuery(query, null);
    	if (c != null)
            c.moveToFirst();
    	Doctor d = new Doctor();
    	d.setName(c.getString(c.getColumnIndex("name")));
    	d.setEmail(c.getString(c.getColumnIndex("email")));
    	return d;
    }
    
    //Retrieve doctor list for a user
    List<Doctor> getDoctors(String username)
    {
    	List<Doctor> doctors = new ArrayList<Doctor>();
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query="SELECT * FROM doctors WHERE doctorUserName = \"" + username + "\""; 
    	Cursor c = db.rawQuery(query, null);
    	if (c.moveToFirst()) {
            do {
            	Doctor d = new Doctor();
            	d.setName(c.getString(c.getColumnIndex("name")));
            	d.setEmail(c.getString(c.getColumnIndex("email")));
                // adding to doctors list
                doctors.add(d);
            } while (c.moveToNext());
        }
    	return doctors;
    }
    
    //add Contact, returns name on success
    String addContact(Contact c, String username)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	 
        ContentValues values = new ContentValues();
        values.put("name", c.getName()); // Contact Name
        values.put("email",c.getEmail()); // Contact Email
        values.put("phone",c.getPhone()); // Contact Phone
        values.put("contactUserName",username); // User for this Contact

        // Inserting Row
        try {db.insertOrThrow("contacts", null, values);}
        catch (android.database.sqlite.SQLiteConstraintException ex){ return ex.toString(); }
        return "success";
    }
    
    void deleteContact(String contactName, String username)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query;
    	
    	//delete doses
    	query="DELETE FROM contacts " 
    			+ "WHERE name = \"" + contactName +"\" "
    			+ "AND contactUsername = \"" + username +"\"";
    	db.execSQL(query);
    }
    
    //Retrieve contact info from database
    Contact getContactInfo(String name, String username)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query="SELECT * FROM contacts WHERE contactUserName = \"" + username + "\" AND name = \"" + name + "\"";
    	Cursor c = db.rawQuery(query, null);
    	if (c != null)
            c.moveToFirst();
    	Contact con = new Contact();
    	con.setName(c.getString(c.getColumnIndex("name")));
    	con.setEmail(c.getString(c.getColumnIndex("email")));
    	con.setPhone(c.getString(c.getColumnIndex("phone")));
    	return con;
    }
    
    //Retrieve contact list for a user
    List<Contact> getContacts(String username)
    {
    	List<Contact> contacts = new ArrayList<Contact>();
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query="SELECT * FROM contacts WHERE contactUserName = \"" + username + "\""; 
    	Cursor c = db.rawQuery(query, null);
    	if (c.moveToFirst()) {
            do {
            	Contact con = new Contact();
            	con.setName(c.getString(c.getColumnIndex("name")));
            	con.setEmail(c.getString(c.getColumnIndex("email")));
            	con.setPhone(c.getString(c.getColumnIndex("phone")));
                // adding to contacts list
                contacts.add(con);
            } while (c.moveToNext());
        }
    	return contacts;
    }
    
    //add dose for a user's medication
    String addDose(Dose d, String username, String genericName)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	 
        ContentValues values = new ContentValues();
        values.put("doseUsername",username); // User for this Dose
        values.put("doseGenericName",genericName); // Medication for this Dose
        values.put("hour",d.getHour()); // hour
        values.put("minute",d.getMinute()); // minute
        values.put("amount", d.getAmount()); // Dose amount
        values.put("type",d.getType()); // Dose type
        values.put("minute",d.getMinute()); // minute
        values.put("retry", 0); //retry count

        // Inserting Row
        db.insert("doses", null, values);
        return d.getAmount() + " " + d.getType() +" at " + d.getHour() +":"+ d.getMinute();
    }
    
    //get retry count for a dose
    int getRetryCount(String username, String genericName)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	String query="Select retry FROM doses WHERE doseUsername = \"" + username + "\" AND doseGenericName = \"" + genericName + "\"";
    	Cursor c = db.rawQuery(query, null);
    	int count = 0;
    	if (c.moveToFirst() ){
    	   	count=c.getInt(c.getColumnIndex("retry"));
    	}
    	return count;
    }
    
    //set retry count for a dose
    void setRetryCount(String username, String genericName, int count)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
    	String query="UPDATE doses "
        		+ "SET retry = \"" + count +"\" "
        		+ "WHERE doseUsername = \"" + username + "\" AND doseGenericName = \"" + genericName + "\"";
        db.execSQL(query);
    }
    
    
    //get list of doses for a user and medicine
    List<Dose> getDoses (String username, String genericName)
    {
    	List<Dose> doses = new ArrayList<Dose>();
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query="SELECT * FROM doses  WHERE doseUsername = \"" + username + "\" AND doseGenericName = \"" + genericName + "\""; 
    	Cursor c = db.rawQuery(query, null);
    	if (c.moveToFirst()) {
            do {
            	Dose d = new Dose();
            	d.setAmount(c.getString(c.getColumnIndex("amount")));
            	d.setType(c.getString(c.getColumnIndex("type")));
            	d.setHour(c.getInt(c.getColumnIndex("hour")));
            	d.setMinute(c.getInt(c.getColumnIndex("minute")));
                // adding to contacts list
                doses.add(d);
            } while (c.moveToNext());
        }
    	return doses;
    }
    
    //delete a dose
    void deleteDose(Dose d, String username, String genName)
    {
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query;
    	
    	//delete doses
    	query="DELETE FROM doses " 
    			+ "WHERE doseUsername = \"" + username +"\" "
    			+ "AND doseGenericName = \"" + genName +"\" "
    			+ "AND hour = \"" + d.getHour() +"\" "
    			+ "AND minute = \"" + d.getMinute() +"\"";
    	db.execSQL(query);
    }
    
    
    //update medication
    void updateMedication(Medication old, Medication m, String username)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
        String query="UPDATE medications "
        		+ "SET name = \"" + m.getName() +"\", "
        		+ "genericName = \"" + m.getGenName() +"\" "
        		+ "WHERE genericName = \"" + old.getGenName() + "\" " + "AND medicationUsername = \"" + username+"\" ";
        db.execSQL(query);
        
        //update doses if genericName changes
        if (!old.getGenName().equals(m.getGenName()) )
        {
        	query="UPDATE doses "
            		+ "SET doseGenericName = \"" + m.getGenName() +"\" "
            		+ "WHERE doseGenericName = \"" + old.getGenName() + "\" " + "AND doseUsername = \"" + username+"\" ";
            db.execSQL(query);
        }
    }
    
  //update user
    void updateUser(User old, User u)
    {
    	SQLiteDatabase db = this.getWritableDatabase();
        String query="UPDATE users "
        		+ "SET name = \"" + u.getName() +"\", "
        		+ "username = \"" + u.getUserName() +"\", "
        		+ "password = \"" + u.getPassword() +"\", "
        		+ "email = \"" + u.getEmail()+"\", "
        		+ "birthday = \"" + u.getBirthday() +"\" "
        		+ "WHERE username = \"" + old.getUserName() + "\" ";
        db.execSQL(query);
        
       //update other database tables if username changes
        if(!old.getUserName().equals(u.getUserName()))
        {
        	query = "UPDATE doctors SET doctorUsername = \"" + u.getUserName() + "\" WHERE doctorUsername = \"" + old.getUserName() + "\"";
        	db.execSQL(query);
        	query = "UPDATE medications SET medicationUsername = \"" + u.getUserName() + "\" WHERE medicationusername = \"" + old.getUserName() + "\"";
        	db.execSQL(query);
        	query = "UPDATE contacts SET contactUsername = \"" + u.getUserName() + "\" WHERE contactUsername = \"" + old.getUserName() + "\"";
        	db.execSQL(query);
        	query = "UPDATE doses SET doseUsername = \"" + u.getUserName() + "\" WHERE doseUsername = \"" + old.getUserName() + "\"";
        	db.execSQL(query);
        	query = "UPDATE notes SET noteUsername = \"" + u.getUserName() + "\" WHERE noteUsername = \"" + old.getUserName() + "\"";
        	db.execSQL(query);
        	query = "UPDATE vitals SET vitalsUsername = \"" + u.getUserName() + "\" WHERE vitalsUsername = \"" + old.getUserName() + "\"";
        	db.execSQL(query);
        }
    }
  //add Vitals
    boolean addVitals(VitalSigns v, String username){
    	SQLiteDatabase db = this.getWritableDatabase();
    	boolean added = true;
    	Calendar c = Calendar.getInstance();
        c.setTime(v.getDate());
    	ContentValues values;
    	//check if user already has vitals stored for that day. If not, add
    	//if(getVitals(v.getDate(), username) == null){
    		values = new ContentValues();
    		values.put("vitalsUsername", username); // Username who added vitals
            values.put("cholesterol", v.getCholesterol()); 
            values.put("glucose", v.getBloodGlucose()); 
            values.put("systolic", v.getSystolicBP());
            values.put("diastolic", v.getDiastolicBP());
            values.put("year", c.get(Calendar.YEAR));
            values.put("month", c.get(Calendar.MONTH));
            values.put("day",  c.get(Calendar.DAY_OF_MONTH));
            // Inserting Row
            if( db.insert("vitals", null, values) == -1)
            	added = false;


    	//}
    	return added; 
    }//end of addVitals
    
    //Retrieve vitals info from database
    VitalSigns getVitals(Date vDate, String username){
    	SQLiteDatabase db = this.getReadableDatabase();
    	Calendar cal = Calendar.getInstance();
    	cal.setTime(vDate);
    	int year = cal.get(Calendar.YEAR), month = cal.get(Calendar.MONTH), day =cal.get(Calendar.DAY_OF_MONTH);
    	
    	String query="SELECT cholesterol, glucose, systolic, diastolic FROM vitals "
    			+ "WHERE year = \"" + year + "\" AND month = \"" + month + "\" AND"
    					+ " day = \"" +day + "\"";
    	Cursor c = db.rawQuery(query, null);
    	if (c != null){
            if(c.moveToFirst()==true){
	            VitalSigns v = new VitalSigns();
	            v.setCholesterol(Integer.parseInt(c.getString(c.getColumnIndex("cholesterol"))));
	            v.setBloodGlucose((Integer.parseInt(c.getString(c.getColumnIndex("glucose")))));
	            v.setSystolicBP((Integer.parseInt(c.getString(c.getColumnIndex("systolic")))));
	            v.setDiastolicBP((Integer.parseInt(c.getString(c.getColumnIndex("diastolic")))));
	            v.setDate(vDate); //date already given
	            return v;
            }
            return null;
    	}
    	return null;
    }
    
    
    //retrieve list of vital signs for a user
    ArrayList<VitalSigns> getVitalsList(String username){
    	ArrayList<VitalSigns> vitals = new ArrayList<VitalSigns>();

    	String query="SELECT cholesterol, glucose, systolic, diastolic, year, month, day " 
    			+ "FROM vitals "
    			+ "WHERE vitalsUsername = \"" + username +"\"";	
    	
    	SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(query, null);

        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                VitalSigns v = new VitalSigns();
                v.setCholesterol(Integer.parseInt(c.getString(c.getColumnIndex("cholesterol"))));
	            v.setBloodGlucose((Integer.parseInt(c.getString(c.getColumnIndex("glucose")))));
	            v.setSystolicBP((Integer.parseInt(c.getString(c.getColumnIndex("systolic")))));
	            v.setDiastolicBP((Integer.parseInt(c.getString(c.getColumnIndex("diastolic")))));
	            
	            Calendar d = Calendar.getInstance();//make a date object
	            int year = (Integer.parseInt(c.getString(c.getColumnIndex("year"))));
	            int month = (Integer.parseInt(c.getString(c.getColumnIndex("month"))));
	            int day = (Integer.parseInt(c.getString(c.getColumnIndex("day"))));
	            d.set(year, month, day); //change to date from DB
	            v.setDate(d.getTime()); 
	            
                // adding to vitals list
                vitals.add(v);
            } while (c.moveToNext());
        }
    	return vitals;
    }
  //delete a user's vital sign given the date
    void deleteVitalSign(Date date, String username){
    	SQLiteDatabase db = this.getReadableDatabase();
    	String query;
    	int year = date.getYear();
    	int month = date.getMonth();
    	int day = date.getDay();
    	
    	//delete vitals
    	query="DELETE FROM vitals " 
    			+ "WHERE vitalsUsername = \"" + username +"\" "
    			+ "AND year = \"" + year +"\" "
    			+ "AND month =\"" + month +"\" "
    			+ "AND day =\"" + day +"\"";
    	db.execSQL(query);
    }
    
    //Add meal
     void addMeal(Meal m)
     {
     	SQLiteDatabase db = this.getWritableDatabase();
     	 
         ContentValues values = new ContentValues();

         values.put("meal",m.getMeal());
         values.put("minute", m.getMealCal());
         values.put("amount", m.getMealDate());
         values.put("type",m.getMealNote());

         db.insert("meals", null, values);
     }
     
     //Get meal
     Meal getMeal(String username)
     {
     	SQLiteDatabase db = this.getReadableDatabase();
     	String query="SELECT * FROM meal WHERE mealsUserName = \"" + username + "\"";
     	Cursor c = db.rawQuery(query, null);
     	if (c != null)
     	{
             c.moveToFirst();
     	}
     	Meal m = new Meal();
     	m.setMeal(c.getString(c.getColumnIndex("meal")));
     	m.setMealDate(c.getString(c.getColumnIndex("date")));
     	m.setMealCal(c.getString(c.getColumnIndex("cal")));
     	m.setMealNote(c.getString(c.getColumnIndex("note")));
     	return m;
     }
     
     //retrieve list of vital signs for a user
     ArrayList<Meal> getMealsList(String username)
     {
     	ArrayList<Meal> meals = new ArrayList<Meal>();

     	String query="SELECT meal, date, cal, note, " 
     			+ "FROM vitals " + "WHERE mealsUsername = \"" + username +"\"";	
     	
     	SQLiteDatabase db = this.getReadableDatabase();
         Cursor c = db.rawQuery(query, null);

         // looping through all rows and adding to list
         if (c.moveToFirst()) 
         {
             do 
             {
                 Meal m = new Meal();
                 m.setMeal(c.getString(c.getColumnIndex("meal")));
 	            m.setMealDate(c.getString(c.getColumnIndex("date")));
 	            m.setMealCal(c.getString(c.getColumnIndex("cal")));
 	            m.setMealNote(c.getString(c.getColumnIndex("note")));
 	            
                 meals.add(m);
             } while (c.moveToNext());
         }
     	return meals;
     }
     	
     ///////////////////// SEARCH /////////////////////
     
     List<Doctor> searchDoctors(String username, String search)
     {
     	List<Doctor> doctors = new ArrayList<Doctor>();
     	SQLiteDatabase db = this.getReadableDatabase();
     	String query="SELECT * FROM doctors WHERE doctorUserName = \"" + username + "\" AND name = \"" + search + "\""; 
     	Cursor c = db.rawQuery(query, null);
     	if (c.moveToFirst()) 
     	{
             do 
             {
             	Doctor d = new Doctor();
             	d.setName(c.getString(c.getColumnIndex("name")));
             	d.setEmail(c.getString(c.getColumnIndex("email")));
             	
                 doctors.add(d);
             } while (c.moveToNext());
         }
     	return doctors;
     }
     
     List<Contact> searchContacts(String username, String search)
     {
     	List<Contact> contacts = new ArrayList<Contact>();
     	SQLiteDatabase db = this.getReadableDatabase();
     	String query="SELECT * FROM contacts WHERE contactUserName = \"" + username + "\" AND name = \"" + search + "\""; 
     	Cursor c = db.rawQuery(query, null);
     	if (c.moveToFirst()) 
     	{
             do
             {
             	Contact con = new Contact();
             	con.setName(c.getString(c.getColumnIndex("name")));
             	con.setEmail(c.getString(c.getColumnIndex("email")));
             	con.setPhone(c.getString(c.getColumnIndex("phone")));

             	contacts.add(con);
             } while (c.moveToNext());
         }
     	return contacts;
     }
     
     List<Medication> searchMedications(String username, String search)
     {
     	List<Medication> medications = new ArrayList<Medication>();

     	String query="SELECT name, genericName " 
     			+ "FROM medications "
     			+ "WHERE medicationUsername = \"" + username + "\" AND name = \"" + search + "\"";
     	
     	SQLiteDatabase db = this.getReadableDatabase();
         Cursor c = db.rawQuery(query, null);

         if (c.moveToFirst()) 
         {
             do {
                 Medication m = new Medication();
                 m.setName(c.getString(c.getColumnIndex("name")));
                 m.setGenName(c.getString(c.getColumnIndex("genericName")));

                 medications.add(m);
             } while (c.moveToNext());
         }
     	return medications;
     }
    
    //close the database
    public void closeDB() {
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null && db.isOpen())
            db.close();
    }

}
